package cn.fisok.pdman.command.impl;

import cn.fisok.pdman.command.ExecResult;
import cn.fisok.pdman.dbreverse.*;
import cn.fisok.raw.kit.JdbcKit;
import cn.fisok.raw.kit.StringKit;
import cn.fisok.raw.kit.UpOrLow;

import java.sql.*;
import java.util.*;

/**
 * 数据库逆向解析功能
 */
public class DBReverseParseCommand extends AbstractDBCommand<ExecResult> {
    public ExecResult exec(Map<String, String> params) {
        super.init(params);
        ExecResult ret = new ExecResult();

        Connection conn = null;
        String dbType = null;
        try {
            conn = JdbcKit.getConnection(driverClassName, url, username, password);
            //ORACLE使用SQL查询字段信息
            dbType = StringKit.nvl(conn.getMetaData().getDatabaseProductName(),"MYSQL").toUpperCase();
        } catch (Exception e) {
            ret.setStatus(ExecResult.FAILED);
            ret.setBody(e.getMessage());
            logger.error("", e);
            return ret;
        }

        //解析并填充数据模型
        ParseDataModel dataModel = new ParseDataModel();
        dataModel.setDbType(dbType);

        parseFill(dataModel,dbType, conn, params.get("flag"));
        ret.setBody(dataModel);
        ret.setStatus(ExecResult.SUCCESS);

        return ret;
    }

    protected void parseFill(ParseDataModel dataModel, String dbType, Connection conn, String flag) {
        List<Entity> entities = getTableEntityList(dbType,conn,dataModel,flag);
//        entities.forEach(entity -> {
//            System.out.println(entity.getTitle()+"|"+entity.getChnname());
//            entity.getFields().forEach(field -> {
//                System.out.println("\t"+field);
//            });
//        });

        Module module = new Module();
        module.setCode("DB_REVERSE_"+dbType.toUpperCase());
        module.setName("逆向解析_"+dbType.toUpperCase());
        module.setEntities(entities);
        dataModel.setModule(module);
    }

    protected List<Entity> getTableEntityList(String dbType, Connection conn, ParseDataModel dataModel, String flag) {
        List<Entity> tableEntities = new ArrayList<Entity>();
        DatabaseMetaData dbmd = null;
        ResultSet rs = null;
        Statement stmt = null;
        try {
            dbmd = conn.getMetaData();
            String schemaPattern = null;
            if("ORACLE".equalsIgnoreCase(dbType)||"DB2".equalsIgnoreCase(dbType)||"DM DBMS".equalsIgnoreCase(dbType)) {
                schemaPattern = conn.getMetaData().getUserName().toUpperCase();
            }
            rs = dbmd.getTables(conn.getCatalog(), schemaPattern, "%", new String[]{"TABLE"});
            stmt = rs.getStatement();
            while (rs.next()) {
                /**
                 *  SQL Server系统保留表
                 *  trace_xe_action_map,trace_xe_event_map
                 */
                if(!rs.getString("TABLE_NAME").equalsIgnoreCase("PDMAN_DB_VERSION")
                        && !rs.getString("TABLE_NAME").equalsIgnoreCase("trace_xe_action_map")
                        && !rs.getString("TABLE_NAME").equalsIgnoreCase("trace_xe_event_map")) {

                    String originTableName = rs.getString("TABLE_NAME");
                    String tableName = adjustUpOrLow(rs.getString("TABLE_NAME"), flag);
                    String remarks = rs.getString("REMARKS");
                    Entity entity = new Entity();
                    entity.setTitle(tableName);
                    entity.setChnname(remarks);

                    parseFillTable(dbType,entity,conn,dataModel,flag,originTableName);

                    tableEntities.add(entity);
                }
            }
        } catch (SQLException e) {
            logger.error("读取表清单出错",e);
            throw new RuntimeException(e);
        } finally {
            JdbcKit.close(stmt);
            JdbcKit.close(rs);
        }

        return tableEntities;
    }

    //oracle和db2需要
    private static String getSchema(String dbType,Connection conn) throws SQLException {
        String schema = null;
        if("ORACLE".equalsIgnoreCase(dbType)||"DB2".equalsIgnoreCase(dbType)){
            schema = conn.getMetaData().getUserName().toUpperCase();
            if ((schema == null) || (schema.length() == 0)) {
                throw new RuntimeException(dbType+"数据库,schema不允许为空");
            }
        }
        return schema;
    }

    protected void parseFillTable(String dbType, Entity entity, Connection conn, ParseDataModel dataModel, String flag, String originTableName) {

//        String tableName = entity.getTitle();
        ResultSet rs = null;
        Statement stmt = null;
        ResultSet pkRs = null;
        Statement pkStmt = null;

        try {
            DatabaseMetaData connMetaData = conn.getMetaData();
            if("MICROSOFT SQL SERVER".equals(dbType)||dbType.startsWith("MICROSOFT")) {
                rs = connMetaData.getColumns(null,null,originTableName,null);
                pkRs = connMetaData.getPrimaryKeys(null,null,originTableName);
            } else {
                rs = connMetaData.getColumns(conn.getCatalog(), getSchema(dbType,conn),originTableName, "%");
                pkRs = connMetaData.getPrimaryKeys(conn.getCatalog(),getSchema(dbType,conn),originTableName);
            }
            stmt = rs.getStatement();
            pkStmt = pkRs.getStatement();
            Set<String> pkSet = new HashSet<String>();
            while(pkRs.next()){
                String columnName = pkRs.getString("COLUMN_NAME");
                pkSet.add(adjustUpOrLow(columnName,flag));
            }

//            System.out.println("------>A");
//            for(int i=1;i<=rs.getMetaData().getColumnCount();i++){
//                System.out.println(rs.getMetaData().getColumnName(i));
//            }
//            System.out.println("------>Z");
            while(rs.next()){
                String colName = adjustUpOrLow(rs.getString("COLUMN_NAME"),flag);
                String remarks = rs.getString("REMARKS");
                String typeName = rs.getString("TYPE_NAME");
                int dataType = rs.getInt("DATA_TYPE");
//                String tableName = rs.getString("TABLE_NAME");
                String isAutoincrement = null;
                if(dbType.equals("MYSQL")) {
                    isAutoincrement = rs.getString("IS_AUTOINCREMENT");
                }
                int columnSize = rs.getInt("COLUMN_SIZE");
                int decimalDigits = rs.getInt("DECIMAL_DIGITS");
                String isNullable = rs.getString("IS_NULLABLE");

//                System.out.println(colName+" IS "+columnSize+","+decimalDigits);
//                System.out.println(colName+" IS "+isNullable);

                Field field = new Field();
                field.setChnname(remarks);
                field.setName(colName);
                field.setType(typeName+"("+columnSize+","+decimalDigits+")");
                field.setPk(pkSet.contains(colName));
                if(dbType.equals("MYSQL")) {
                    field.setAutoIncrement(!"NO".equalsIgnoreCase(isAutoincrement));
                }
                field.setNotNull(!"YES".equalsIgnoreCase(isNullable));
//                if("INT".equals(typeName)){
//                    System.out.println(columnSize);
//                }
                DataType domainDataType = touchDataType(typeName,dataType,columnSize,decimalDigits,dataModel);
                field.setType(domainDataType.getCode());


                entity.getFields().add(field);
            }
        } catch (SQLException e) {
            logger.error("读取数据表"+originTableName+"的字段明细出错",e);
            throw new RuntimeException("读取数据表"+originTableName+"的字段明细出错",e);
        } finally {
            JdbcKit.close(stmt);
            JdbcKit.close(rs);
            JdbcKit.close(pkStmt);
            JdbcKit.close(pkRs);

        }
    }

    private DataType touchDataType(String typeName, int dataType, int columnSize, int decimalDigits, ParseDataModel dataModel){
        List<String> atomList = new ArrayList<String>();
        List<String> lenList = new ArrayList<String>();
        atomList.add(typeName);


        String domainTypeName = typeName;
        if(JdbcKit.isNumeric(dataType)){
            atomList.add(""+columnSize);
            lenList.add(""+columnSize);
            if(decimalDigits>0){
                atomList.add(""+decimalDigits);
                lenList.add(""+decimalDigits);
            }
            domainTypeName+="("+ StringKit.join(lenList,",")+")";
        }else if(JdbcKit.isShortString(dataType)){
            atomList.add(""+columnSize);
            lenList.add(""+columnSize);
            domainTypeName+="("+ StringKit.join(lenList,",")+")";
        }

        String domainName = StringKit.join(atomList,"_");
        Map<String, DataType> dataTypeMap = dataModel.getDataTypeMap();
        DataType domainDataType = dataTypeMap.get(domainName);
        if(domainDataType==null){
            domainDataType = new DataType();
            domainDataType.setName(domainName);
            domainDataType.setCode(domainName);
            domainDataType.setType(domainTypeName);
            dataTypeMap.put(domainName,domainDataType);
        }

        return domainDataType;

    }

    private String adjustUpOrLow(String tableName, String flag) {
        flag = StringKit.nvl(flag,"");
        if(flag.equals(UpOrLow.DEFAULT.toString())) {
            return tableName;
        }else if(flag.equals(UpOrLow.LOWCASE.toString())){
            return tableName.toLowerCase();
        }else {
            return tableName.toUpperCase();
        }
    }

}
